package cn.interestingshop.dao.impl;

import cn.interestingshop.dao.BaseDao;
import cn.interestingshop.dao.GoodsDao;
import cn.interestingshop.entity.Goods;
import cn.interestingshop.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class GoodsDaoImpl extends BaseDao implements GoodsDao {
    @Override
    public List<Goods> selectAll(int pageNo, int pageSize) {
        List<Goods> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1. 加载驱动
            //2. 获取连接对象
            conn = getConnection();
            //3. 定义sql语句
            String sql = "SELECT * FROM `interesting_shop_db`.`t_goods` LIMIT ?, ?";
            //4. 创建执行对象
            ps = conn.prepareStatement(sql);
            //5. 填充参数
            ps.setInt(1, (pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            //6. 执行sql语句
            rs = ps.executeQuery();
            //7. 分析返回结果
            while (rs.next()) {
                Goods goods=new Goods();
                goods.setId(rs.getInt("id"));
                goods.setGoodsName(rs.getString("goods_name"));
                goods.setGoodsDesc(rs.getString("goods_desc"));
                goods.setPrice(rs.getFloat("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setClassifyLevel1Id(rs.getInt("classify_level1_id"));
                goods.setClassifyLevel2Id(rs.getInt("classify_level2_id"));
                goods.setClassifyLevel3Id(rs.getInt("classify_level3_id"));
                goods.setFileName(rs.getString("file_name"));
                goods.setIsDelete(rs.getInt("is_delete"));
                list.add(goods);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //8.释放资源关闭连接
            closeAll(rs, ps, conn);
        }
        return list;
    }

    @Override
    public int selectCount() {
        int count=0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1. 加载驱动
            //2. 获取连接对象
            conn = getConnection();
            //3. 定义sql语句
            String sql = "SELECT count(1) FROM `t_goods`";
            //4. 创建执行对象
            ps = conn.prepareStatement(sql);
            //5. 填充参数
            //6. 执行sql语句
            rs = ps.executeQuery();
            //7. 分析返回结果
            while (rs.next()) {
                count=rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //8.释放资源关闭连接
            closeAll(rs, ps, conn);
        }
        return count;
    }

    @Override
    public int insert(Goods goods) {
        String sql="INSERT INTO `interesting_shop_db`.`t_goods`" +
                "(`goods_name`, `goods_desc`, `price`, `stock`, `classify_level1_id`, `classify_level2_id`, `classify_level3_id`, `file_name`, `is_delete`) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        return executeUpdate(sql,new Object[]{
                goods.getGoodsName(),
                goods.getGoodsDesc(),
                goods.getPrice(),
                goods.getStock(),
                goods.getClassifyLevel1Id(),
                goods.getClassifyLevel2Id(),
                goods.getClassifyLevel3Id(),
                goods.getFileName(),
                goods.getIsDelete()
        });
    }

    @Override
    public Goods selectOne(int id) {
        Goods l=null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1. 加载驱动
            //2. 获取连接对象
            conn = getConnection();
            //3. 定义sql语句
            String sql = "SELECT * FROM `interesting_shop_db`.`t_goods` where id=?";
            //4. 创建执行对象
            ps = conn.prepareStatement(sql);
            //5. 填充参数
            ps.setInt(1, id);
            //6. 执行sql语句
            rs = ps.executeQuery();
            //7. 分析返回结果
            while (rs.next()) {
                l=new Goods();
                l.setId(rs.getInt("id"));
                l.setGoodsName(rs.getString("goods_name"));
                l.setGoodsDesc(rs.getString("goods_desc"));
                l.setPrice(rs.getFloat("price"));
                l.setStock(rs.getInt("stock"));
                l.setClassifyLevel1Id(rs.getInt("classify_level1_id"));
                l.setClassifyLevel2Id(rs.getInt("classify_level2_id"));
                l.setClassifyLevel3Id(rs.getInt("classify_level3_id"));
                l.setFileName(rs.getString("file_name"));
                l.setIsDelete(rs.getInt("is_delete"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //8.释放资源关闭连接
            closeAll(rs, ps, conn);
        }
        return l;
    }

    @Override
    public int update(Goods goods) {
        String sql="UPDATE `interesting_shop_db`.`t_goods` " +
                "SET `goods_name` = ?, " +
                "`goods_desc` = ?," +
                " `price` = ?, " +
                "`stock` = ?, " +
                "`classify_level1_id` = ?, " +
                "`classify_level2_id` = ?, " +
                "`classify_level3_id` = ?, " +
                "`file_name` = ?" +
                " WHERE `id` = ?";
        return executeUpdate(sql,new Object[]{
                goods.getGoodsName(),
                goods.getGoodsDesc(),
                goods.getPrice(),
                goods.getStock(),
                goods.getClassifyLevel1Id(),
                goods.getClassifyLevel2Id(),
                goods.getClassifyLevel3Id(),
                goods.getFileName(),
                goods.getId()
        });
    }


    @Override
    public int delete(int id) {
        String sql="DELETE FROM `interesting_shop_db`.`t_goods` WHERE `id` = ?";
        return executeUpdate(sql,new Object[]{
                id
        });
    }

    @Override
    public List<Goods> selectAll(int classifyLevel1Id, int pageNo, int pageSize) {
        List<Goods> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1. 加载驱动
            //2. 获取连接对象
            conn = getConnection();
            //3. 定义sql语句
            String sql = "SELECT * FROM `interesting_shop_db`.`t_goods` where classify_level1_id=? LIMIT ?, ?";
            //4. 创建执行对象
            ps = conn.prepareStatement(sql);
            //5. 填充参数
            ps.setInt(1, classifyLevel1Id);
            ps.setInt(2, (pageNo-1)*pageSize);
            ps.setInt(3,pageSize);
            //6. 执行sql语句
            rs = ps.executeQuery();
            //7. 分析返回结果
            while (rs.next()) {
                Goods goods=new Goods();
                goods.setId(rs.getInt("id"));
                goods.setGoodsName(rs.getString("goods_name"));
                goods.setGoodsDesc(rs.getString("goods_desc"));
                goods.setPrice(rs.getFloat("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setClassifyLevel1Id(rs.getInt("classify_level1_id"));
                goods.setClassifyLevel2Id(rs.getInt("classify_level2_id"));
                goods.setClassifyLevel3Id(rs.getInt("classify_level3_id"));
                goods.setFileName(rs.getString("file_name"));
                goods.setIsDelete(rs.getInt("is_delete"));
                list.add(goods);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //8.释放资源关闭连接
            closeAll(rs, ps, conn);
        }
        return list;
    }
}
